package com.noteit.dao.impl;

import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.Restrictions;

import com.noteit.dao.NoteitDAO;
import com.noteit.bean.*;

public class NoteitDAOImpl implements NoteitDAO {
	private SessionFactory sessionFactory;

	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	public void updateUploadLocation(String userName, String deleteImage){
		Session session = null; 
		String sql = "UPDATE NOTEIT SET picture=null WHERE " +
			         "deleteFlag='N' AND userName='"+userName+"' AND picture='"+deleteImage+"' ";
		try {
			session = sessionFactory.openSession();
			Transaction t = session.beginTransaction();
			session.createSQLQuery(sql).executeUpdate();
			t.commit();
		} catch (HibernateException e) {
			e.printStackTrace();
		}finally{
			session.close();
		}
		return;
	}
	public List<Noteit> wordsPoolCondition(Noteit noteit,Date createdateBgn, Date createdateEnd, Date updatedateBgn, Date updatedateEnd){
		Session session = null;
		session = sessionFactory.openSession();
		Criteria c = session.createCriteria(Noteit.class);
		
		String userName = noteit.getUserName();
		String english = noteit.getEnglish();
		String chinese = noteit.getChinese();
		String deleteFlag = noteit.getDeleteFlag();
		System.out.println("userName="+userName+", english="+english);
		if (userName != null && !userName.isEmpty())
			c.add(Restrictions.eq("userName", userName));
		if (deleteFlag != null && !deleteFlag.isEmpty())
			c.add(Restrictions.eq("deleteFlag", deleteFlag));
		if (english != null && !english.isEmpty())
			c.add(Restrictions.like("english", english + "%"));
		if (chinese != null && !chinese.isEmpty())
			c.add(Restrictions.like("chinese", "%" + chinese + "%"));
		if (createdateBgn != null && createdateEnd != null)
			c.add(Restrictions.between("createdate", createdateBgn, createdateEnd));
		if (updatedateBgn != null && updatedateEnd != null)
			c.add(Restrictions.between("updatedate", updatedateBgn, updatedateEnd));
		
		List<Noteit> iList = c.list();
		System.out.println("iList size="+iList.size());
		session.close();
		return iList;
	}
	
	public List<Noteit> wordsPoolDelHistory(Noteit noteit,Date createdateBgn, Date createdateEnd, Date deletedateBgn, Date deletedateEnd){
		Session session = null;
		session = sessionFactory.openSession();
		Criteria c = session.createCriteria(Noteit.class);
		
		String userName = noteit.getUserName();
		String english = noteit.getEnglish();
		String chinese = noteit.getChinese();
		String deleteFlag = noteit.getDeleteFlag();
		System.out.println("userName="+userName+", english="+english);
		if (userName != null && !userName.isEmpty())
			c.add(Restrictions.eq("userName", userName));
		if (deleteFlag != null && !deleteFlag.isEmpty())
			c.add(Restrictions.eq("deleteFlag", deleteFlag));
		if (english != null && !english.isEmpty())
			c.add(Restrictions.like("english", english + "%"));
		if (chinese != null && !chinese.isEmpty())
			c.add(Restrictions.like("chinese", "%" + chinese + "%"));
		if (createdateBgn != null && createdateEnd != null)
			c.add(Restrictions.between("createdate", createdateBgn, createdateEnd));
		if (deletedateBgn != null && deletedateEnd != null)
			c.add(Restrictions.between("deletedate", deletedateBgn, deletedateEnd));
		
		List<Noteit> iListDelHistory = c.list();
		System.out.println("iListDelHistory size="+iListDelHistory.size());
		session.close();
		return iListDelHistory;
	}
	
	public List<Noteit> wordsPoolAll(){
		Session session = null;
		session = sessionFactory.openSession();
		Criteria c = session.createCriteria(Noteit.class);
		List<Noteit> iList = c.list();
		session.close();
		return iList;
	}
	
	public String saveNoteit(Noteit noteit) throws Exception {
		Session session = null;
		session = sessionFactory.openSession();
		try {
			Transaction t = session.beginTransaction();
			session.saveOrUpdate(noteit);
			t.commit();
			session.close();
			return "success";
		} catch (Exception e) {
			if (session.isOpen()) {
				session.close();
			}
			e.printStackTrace();
			return e.getMessage();
		}
	}
	
	/**
	 * 點選『DELETE』,deleteFlag改為"Y".
	 */
	public void noteitDataDelete(int id,  String deleteUser) {
		Session session = null;
		String sql = "UPDATE NOTEIT SET deleteFlag='Y', deletedate=NOW(), deleteUserName='"+deleteUser+"' WHERE id=" + id + "";
		try {
			session = sessionFactory.openSession();
			Transaction t = session.beginTransaction();
			session.createSQLQuery(sql).executeUpdate();
			t.commit();
		} catch (HibernateException e) {
			e.printStackTrace();
		}finally{
			session.close();
		}
		return;
	}
	
	/**
	 * 點選『recovery』,deleteFlag改為"Y".
	 */
	public void noteitDataRecoveryDelete(String recoveryId) {
		Session session = null;
		String sql = "UPDATE NOTEIT SET deleteFlag='N', updatedate=NOW() WHERE id='" + recoveryId + "'";
		try {
			session = sessionFactory.openSession();
			Transaction t = session.beginTransaction();
			session.createSQLQuery(sql).executeUpdate();
			t.commit();
		} catch (HibernateException e) {
			e.printStackTrace();
		}finally{
			session.close();
		}
		return;
	}
	
}
